package sk.velkavrana.app.dao.impl;

import org.apache.commons.lang.StringUtils;
import org.hibernate.dialect.*;
import sk.velkavrana.app.dao.DatabaseService;
import sk.velkavrana.app.model.ConnectionInfo;
import sk.velkavrana.app.model.temp.TableInfo;
import sk.velkavrana.app.util.DBUtil;
import sk.velkavrana.preRelease.dialect.InformixExtDialect;
import sk.velkavrana.preRelease.dialect.TeradataExtDialect;

import java.sql.Connection;
import java.util.*;

/**
 * @author mtymes
 * @since May 19, 2009 12:13:31 PM
 */
public class DatabaseServiceImpl implements DatabaseService {

    private final List<String> jdbcDriverClasses = Collections.unmodifiableList(
            Arrays.asList(
                    // oracle
                    "oracle.jdbc.driver.OracleDriver",
                    // ms sql: 2000, 2005, 2008
                    "com.microsoft.sqlserver.jdbc.SQLServerDriver",
                    // db2
                    "com.ibm.db2.jcc.DB2Driver",
                    // db2 client
                    "COM.ibm.db2.jdbc.app.DB2Driver",
                    // db2 i5
                    "com.ibm.as400.access.AS400JDBCDriver",
                    // informix
                    "com.informix.jdbc.IfxDriver",
                    // sybase: ASE, IQ
                    "com.sybase.jdbc3.jdbc.SybDriver",
                    // mySQL
                    "com.mysql.jdbc.Driver",
                    // teradata
                    "com.teradata.jdbc.TeraDriver",
                    // odbc
                    "sun.jdbc.odbc.JdbcOdbcDriver",
                    // h2
                    "org.h2.Driver"
            ));

    private final Map<String, String> urlString;


    {
        Map<String, String> tempUrlStrings = new HashMap<String, String>();

        // oracle
        tempUrlStrings.put("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@<host>:<port1521>:<sid>");
        // ms sql: 2000, 2005, 2008
        tempUrlStrings.put("com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://<host>:<port1433>;DatabaseName=<database>");
        // db2
        tempUrlStrings.put("com.ibm.db2.jcc.DB2Driver", "jdbc:db2://<server>:<port50000>/<database>");
        // db2 client
        tempUrlStrings.put("COM.ibm.db2.jdbc.app.DB2Driver", "");
        // db2 i5
        tempUrlStrings.put("com.ibm.as400.access.AS400JDBCDriver", "jdbc:as400://<server>naming=sql;errors=full");
        // informix
        tempUrlStrings.put("com.informix.jdbc.IfxDriver", "jdbc:informix-sqli://<host>:<port1533>/<database>:informixserver=<dbservername>");
        // sybase: ASE, IQ
        tempUrlStrings.put("com.sybase.jdbc3.jdbc.SybDriver", "jdbc:sybase:Tds:<host>:<port2048>/<database>");
        // mySQL
        tempUrlStrings.put("com.mysql.jdbc.Driver", "jdbc:mysql://<host>:<port3306>/<database>");
        // teradata
        tempUrlStrings.put("com.teradata.jdbc.TeraDriver", "jdbc:teradata://<host>/DATABASE=<dbName>,DBS_PORT=<port>,DATEFORM=ANSIDATE,CHARSET=UTF8");
        // odbc
        tempUrlStrings.put("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:<datasource>");
        // h2
        tempUrlStrings.put("org.h2.Driver", "jdbc:h2:<location>");

        urlString = Collections.unmodifiableMap(tempUrlStrings);
    }


    private final List<String> dialects = Collections.unmodifiableList(
            Arrays.asList(
                    // oracle
                    Oracle9iDialect.class.getName(),
                    Oracle8iDialect.class.getName(),
                    Oracle10gDialect.class.getName(),
                    // ms sql: 2000, 2005, 2008
                    SQLServerDialect.class.getName(),
                    // db2
                    DB2Dialect.class.getName(),
                    DB2390Dialect.class.getName(),
                    DB2400Dialect.class.getName(),
                    // informix
                    InformixDialect.class.getName(),
                    InformixExtDialect.class.getName(),
                    // sybase: ASE, IQ
                    SybaseDialect.class.getName(),
                    Sybase11Dialect.class.getName(),
                    // mySQL
                    MySQLDialect.class.getName(),
                    MySQL5Dialect.class.getName(),
                    // teradata
                    TeradataDialect.class.getName(),
                    TeradataExtDialect.class.getName(),
                    // h2
                    H2Dialect.class.getName()
            )
    );


    public List<String> getAllJDBCDriverClasses() {
        return jdbcDriverClasses;
    }


    public String getUrlFormat(String driverClassName) {
        String urlFormatString = urlString.get(driverClassName);

        return StringUtils.isEmpty(urlFormatString) ? " " : urlFormatString;
    }


    public List<String> getAllDialects() {
        return dialects;
    }


    public List<String> getAllTables(ConnectionInfo connectionInfo) {
        List<String> tables = new ArrayList<String>();

        Connection conn = DBUtil.connectSilently(connectionInfo);

        if (conn != null) {
            try {
                List<TableInfo> tableInfos = DBUtil.getTables(conn);

                String schema = connectionInfo.getSchema();

                for (TableInfo tableInfo : tableInfos) {
                    if (StringUtils.isBlank(schema) ?
                            StringUtils.isBlank(tableInfo.getSchema()) :
                            // todo: maybe don't use IgnoreCase
                            schema.equalsIgnoreCase(tableInfo.getSchema())) {
                        tables.add(tableInfo.getFullName());
                    }
                }


            } finally {
                DBUtil.closeSilently(conn);
            }
        }


        return tables;
    }
}
